using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DTO;
using System.Data.OleDb;
namespace DAO
{
   public class PhieuKhamDAO
    {
        DataProvider Provider;
        public PhieuKhamDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable LayMaPhieuKham()
        {
            Provider.Connect();
            string str = "Select MaPhieuKham From PHIEUKHAM Where MaPhieuKham >= all (Select MaPhieuKham From PhieuKham)";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangPhieuKham()
        {
            Provider.Connect();
            string str = "select * from PhieuKham";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable ThongTinHoaDon()
        {
            Provider.Connect();
            string str = "Select pk.MaPhieuKham, bn.HoTen, bn.GioiTinh, bn.NgaySinh, bn.DiaChi, pk.NgayKham, lb.TenLoaiBenh, pk.TienKhamHienTai, pk.TienThuoc from PHIEUKHAM pk, BENHNHAN bn, LOAIBENH lb where bn.MaBenhNhan = pk.MaBN and lb.MaLoaiBenh = pk.MaLoaiBenh";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public void ThemPhieuKham(PhieuKhamDTO pkDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into PhieuKham(MaBN, MaLoaiBenh, TrieuChung, NgayKham, TienKhamHienTai, TienThuoc, MaPhieuKham) values (?, ?, ?, ?, ?, ?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaBN", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoaiBenh", OleDbType.WChar);
            cmd.Parameters.Add("@TrieuChung", OleDbType.WChar);
            cmd.Parameters.Add("@NgayKham", OleDbType.Date);
            cmd.Parameters.Add("@TienKhamHienTai", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@TienThuoc", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@MaPhieuKham", OleDbType.WChar);

            cmd.Parameters["@MaBN"].Value = pkDto.MaBN;
            cmd.Parameters["@MaLoaiBenh"].Value = pkDto.MaLoaiBenh;
            cmd.Parameters["@TrieuChung"].Value = pkDto.TrieuChung;
            cmd.Parameters["@NgayKham"].Value = pkDto.NgayKham;
            cmd.Parameters["@TienKhamHienTai"].Value = pkDto.TienKhamHienTai;
            cmd.Parameters["@TienThuoc"].Value = pkDto.TienThuoc;
            cmd.Parameters["@MaPhieuKham"].Value = pkDto.MaPhieuKham;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();

        }


        public void XoaPhieuKham(string maPhieuKham)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From PhieuKham Where MaPhieuKham = ?";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaPhieuKham", OleDbType.Char);
            cmd.Parameters["@MaPhieuKham"].Value = maPhieuKham;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }
        //public void SuaPhieuKham(PhieuKhamDTO pkDto)
        //{
        //    Provider.Connect();
        //    // B3: Tao chuoi strSQL thao tac CSDL
        //    string str;
        //    str = "Update PhieuKham Set MaBN = ?,MaLoaiBenh=?, TrieuChung = ?, NgayKham = ?, TienKhamHienTai = ?, TienThuoc = ? Where MaPhieuKham = ? ";
        //    OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

        //    cmd.Parameters.Add("@MaBN", OleDbType.WChar);
        //    cmd.Parameters.Add("@MaLoaiBenh", OleDbType.WChar);
        //    cmd.Parameters.Add("@TrieuChung", OleDbType.WChar);
        //    cmd.Parameters.Add("@NgayKham", OleDbType.Date);
        //    cmd.Parameters.Add("@TienKhamHienTai", OleDbType.LongVarWChar);
        //    cmd.Parameters.Add("@TienThuoc", OleDbType.LongVarWChar);
        //    cmd.Parameters.Add("@MaPhieuKham", OleDbType.WChar);

        //    cmd.Parameters["@MaBN"].Value = pkDto.MaBN;
        //    cmd.Parameters["@MaLoaiBenh"].Value = pkDto.MaLoaiBenh;
        //    cmd.Parameters["@TrieuChung"].Value = pkDto.TrieuChung;
        //    cmd.Parameters["@NgayKham"].Value = pkDto.NgayKham;
        //    cmd.Parameters["@TienKhamHienTai"].Value = pkDto.TienKhamHienTai;
        //    cmd.Parameters["@TienThuoc"].Value = pkDto.TienThuoc;
        //    cmd.Parameters["@MaPhieuKham"].Value = pkDto.MaPhieuKham;

        //    cmd.ExecuteNonQuery();
        //    Provider.Disconnect();
        //}


        public void SuaTienThuoc(PhieuKhamDTO pkDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update PhieuKham Set TienThuoc = ? Where MaPhieuKham = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@TienThuoc", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@MaPhieuKham", OleDbType.WChar);

            cmd.Parameters["@TienThuoc"].Value = pkDto.TienThuoc;
            cmd.Parameters["@MaPhieuKham"].Value = pkDto.MaPhieuKham;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }

        public DataTable TimKiemTheoMaPhieuKham(string maPhieuKham)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From PhieuKham Where MaPhieuKham = " + "'" + maPhieuKham + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@MaPhieuKham", OleDbType.WChar);
            cmd.Parameters["@MaPhieuKham"].Value = maPhieuKham;

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }
       public DataTable LayBangBenhNhanTheoNgayKham(string _ngaykham)
       {
           Provider.Connect();
           string str = "select bn.MaBenhNhan, bn.HoTen, bn.GioiTinh, bn.NgaySinh, bn.DiaChi  from BENHNHAN bn, PHIEUKHAM pk where pk.MaBN = bn.MaBenhNhan and pk.NgayKham like #" + _ngaykham + "# ";
           DataTable dt = new DataTable();
           OleDbDataAdapter da;
           da = new OleDbDataAdapter(str, Provider.cn);
           da.Fill(dt);
           Provider.Disconnect();
           return dt;
       }
    }
}
